#coding: utf8
import datetime
import MySQLdb
from fresh.utils import Utils
from scrapy import log
from fresh.utils import DatabaseDetails

class TwitterFbDB(object):
    """
    host = "mysql1.alwaysdata.com"
    user = "62465_jmgost"
    passw = "sygnium"
    skema = "freshbuzzing_1"
    """

    utils = None

    logLocalName="TWITTER_FB_SCRAPPY"


    def getCursor(self):
        try:
            self.cursor = self.db.cursor()
            self.cursor.execute('SET NAMES utf8;')
            self.cursor.execute('SET CHARACTER SET utf8;')
            self.cursor.execute('SET character_set_connection=utf8;')
        except Exception,e:
            self.utils.log(self.logLocalName, "Error al crear cursor MySQLdb:  " + str(e), self.utils.ERROR)
            #try to reconnect
            self.connectDB()            
            

    def connectDB(self):
        try:
            props = DatabaseDetails()
            self.db = MySQLdb.connect(props.host,props.user,props.passw,props.skema)
            self.db.set_character_set('utf8')
        except Exception,e:
            self.utils.log(self.logLocalName, "Error al conectar a base de datos MySQLdb.connect: " + str(e), self.utils.ERROR)
            return False
        return True


    def readURLMain(self,urls,idRest):

        """
        url='http://www.syngluten.es'
        idRest[0] = '2572'
        urls.append(url)
        """

        """test"""
        """sql = "SELECT ID_RESTAURANT,DS_REST_URL,DS_REST_FACEBOOK, DS_REST_TWITTER FROM MAST_RESTAURANTS where id_restaurant='1376'"""
               
        """ WE CAN RETRIEVE JUST ONE BY THE TIME OTHERWISE REDIRECTS/HTTP ERRORS MAKES NO POSSIBLE TO TRACK WHAT RESTAURANT IS CRAWLING"""   
        sql = "SELECT ID_RESTAURANT,DS_REST_URL,DS_REST_FACEBOOK, DS_REST_TWITTER FROM MAST_RESTAURANTS where LENGTH(DS_REST_URL)>0 and (DS_REST_FACEBOOK is null or length(DS_REST_FACEBOOK)=0 or DS_REST_TWITTER is null or length(DS_REST_TWITTER)=0 ) ORDER BY AUX_DT_TWFB_LAST_CHECKED ASC"
        try:
            self.getCursor()
            self.cursor.execute(sql)
            data = self.cursor.fetchone()
            if data is not None:
                url = str(data[1])
                if not url.startswith('http'):
                    url = 'http://' + url
                self.utils.log(self.logLocalName, "Restaurant fetch: " + url + "id: " + str(data[0]), self.utils.DEBUG)
                urls.append(url)
                idRest[0]=str(data[0])

                
                self.insertTwFb(idRest[0],data[3],data[2])
                
        except Exception,e:
            self.utils.log(self.logLocalName, "Error al ejecutar sentencia SQL en readURLs:  " + str(e), self.utils.ERROR)
        finally:
            self.cursor.close()
        


    def readURLDeep(self,urls,idRest):

        """
        url='http://www.barrufian.es'
        idRest[0] = '2325'
        urls.append(url)
        """
        
        
        """test"""
        """sql = "SELECT ID_RESTAURANT,DS_REST_URL FROM MAST_RESTAURANTS where id_restaurant='1068'"""

        
        """ WE CAN RETRIEVE JUST ONE BY THE TIME OTHERWISE REDIRECTS/HTTP ERRORS MAKES NO POSSIBLE TO TRACK WHAT RESTAURANT IS CRAWLING"""
        sql = "SELECT ID_RESTAURANT,DS_REST_URL FROM MAST_RESTAURANTS where LENGTH(DS_REST_URL)>0 AND (DS_REST_FACEBOOK is NULL or length(DS_REST_FACEBOOK)<1) AND (DS_REST_TWITTER is NULL or length(DS_REST_TWITTER)<1) ORDER BY AUX_DT_TWFB_LAST_CHECKED ASC"
        
        try:
            self.getCursor()
            self.cursor.execute(sql)
            data = self.cursor.fetchone()
            if data is not None:
                url = str(data[1])
                if not url.startswith('http'):
                    url = 'http://' + url
                self.utils.log(self.logLocalName, "Restaurant fetch: " + url + "id: " + str(data[0]) , self.utils.DEBUG)
                urls.append(url)
                idRest[0]=str(data[0])

                
                self.insertTwFb(idRest[0],None,None)
                
        except Exception,e:
            self.utils.log(self.logLocalName, "Error al ejecutar sentencia SQL en readURLs:  " + str(e), self.utils.ERROR)
        finally:
            self.cursor.close()



    
        
    def insertTwFb(self,id_rest,usrTW, usrFB):
        
        try:
            self.getCursor()
            sql = "SELECT  DS_REST_TWITTER, DS_REST_FACEBOOK FROM MAST_RESTAURANTS WHERE ID_RESTAURANT = %s"
            self.cursor.execute(sql,(str(id_rest)))
            data = self.cursor.fetchone()
            if data is not None:
                if data[0] is None or len(data[0].strip())==0:
                    """ no twitter account so let's put the just found"""
                    sql = "UPDATE MAST_RESTAURANTS set DS_REST_TWITTER = %s, AUX_DT_TWFB_LAST_CHECKED=Now() WHERE ID_RESTAURANT = %s" 
                    self.cursor.execute(sql,(usrTW,str(id_rest)))
                    self.utils.log(self.logLocalName, "TWITTER: " + str(usrTW) + " RESTAURENTE: " + str(id_rest), self.utils.DEBUG)
                if data[1] is None or len(data[1].strip())==0:
                    """ no fb account so let's put the just found"""
                    sql = "UPDATE MAST_RESTAURANTS set DS_REST_FACEBOOK = %s, AUX_DT_TWFB_LAST_CHECKED=Now() WHERE ID_RESTAURANT = %s" 
                    self.cursor.execute(sql,(usrFB,str(id_rest)))
                    self.utils.log(self.logLocalName, "FACEBOOK: " + str(usrFB) + " RESTAURENTE: " + str(id_rest), self.utils.DEBUG)
            else:
                """UPDATE MASTER RESTAURANTS WITH BOTH ACCOUNTS"""
                sql = "UPDATE MAST_RESTAURANTS set DS_REST_TWITTER = %s, DS_REST_FACEBOOK = %s, AUX_DT_TWFB_LAST_CHECKED=Now() WHERE ID_RESTAURANT = %s" 
                self.cursor.execute(sql,(usrTW,usrFB,str(id_rest)))
                self.utils.log(self.logLocalName, "TWITTER: " + str(usrTW) + " FACEBOOK: " + str(usrFB) + " RESTAURENTE: " + str(id_rest), self.utils.DEBUG)

        except Exception,e:
            self.utils.log(self.logLocalName, "Error al ejecutar sentencia SQL en insertTwFb:  " + str(e), self.utils.ERROR)
            self.db.rollback()
        else:
            self.db.commit()
        finally:
            self.cursor.close()
        

    def __del__(self):
        self.db.close()
        super(TwitterFbDB, self).__del__()
    